package cvs;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.StringBufferInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CVSMain {

	public static void main(String[] args) {
		System.out.println("start---");

		// /buildGSBN_compact();
		// buildCVS("tb_OptCompetitor", "select * from tb_OptCompetitor");
		// buildCVS("tb_OptMaster", "select * from v_OptMaster ");
		// buildCVS("tb_CustomerSAP", "select * from tb_CustomerSAP ");

		// buildCVS("tb_OptMaster_",
		// "SELECT distinct optguid, ProbabilityCode, Status, ContactID FROM tb_OptMaster");
		// buildCVS("tb_CustomerOrg_",
		// "select orgid, owner, RegionCode from  dbo.tb_CustomerOrg where valid=1 and account_type is not null");
		// buildCVS("tb_People_", "select PeopleID, RegionCode from tb_People");

		System.out.println("finished---");
	}

	private static void buildGSBN_compact() {
		// accounts
		buildCVS("tb_CustomerOrg", "SELECT * from v_CustomerOrg");
		// buildCVS("tb_CustomerBIZinfo", "select * from v_CustomerBIZinfo");
		buildCVS("tb_SPL_CustomerEnd", "select * from v_SPL_CustomerEnd");
		buildCVS("tb_CustomerSAP", "select * from tb_CustomerSAP ");
		buildCVS("tb_Cust_Brands_Carried",
				"select * from v_Cust_Brands_Carried ");
		buildCVS("tb_Cust_Vertical_Market",
				"select * from v_Cust_Vertical_Market ");

		// user
		buildCVS("tb_People", "select * from v_People ");
		// buildCVS("tb_user", "select * from v_user ");

		// opportunity
		buildCVS("tb_OptMaster", "select * from v_OptMaster ");
		buildCVS("tb_OPTSPL_Master", "select * from tb_OPTSPL_Master");
		buildCVS("tb_OptProduct", "select * from tb_OptProduct");
		buildCVS("tb_OPTSPL_Product", "select * from tb_OPTSPL_Product ");
		buildCVS("tb_OPTSPL_Rollout", "select * from v_OPTSPL_Rollout ");
		buildCVS("tb_OptSPL_Team", "select * from tb_OptSPL_Team");

		// seed
		buildCVS("tb_OptSeedUnit", "select * from tb_OptSeedUnit");
		buildCVS("tb_OptSeedUnitCustomer",
				"select * from v_OptSeedUnitCustomer");
		buildCVS("tb_OptSeedUnitEvalNotes",
				"select * from tb_OptSeedUnitEvalNotes");

		// product
		buildCVS("tb_PriceGroup", "select * from tb_PriceGroup");
		buildCVS("tb_MaterialMaster", "select * from tb_MaterialMaster ");
		buildCVS("tb_MaterialPricing", "select * from v_MaterialPricing ");
		buildCVS("tb_ProductToAccessory", "select * from tb_ProductToAccessory");

		// competitor
		buildCVS("tb_OptCompetitor", "select * from tb_OptCompetitor");

		// partner
		// buildCVS("tb_OptPartner_indir", "select * from v_OptPartner_indir ");
		// buildCVS("tb_OptPartner_dir", "select * from v_OptPartner_dir ");
		// buildCVS("tb_OptPartnerContact",
		// "select * from v_OptPartnerContact ");

		// partner2
		buildCVS("tb_OptPartner2", "select * from v_optPartner2 ");
	}

	public static void buildCVS(String tableName, String sql) {
		System.out.println("table name:" + tableName);
		PreparedStatement ps;
		ResultSet rs;

		DBConnectorGSBN connector = new DBConnectorGSBN();

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			CSVGenerator generator = new CSVGenerator(new File("c:\\gsbndb\\"
					+ tableName + ".csv"));
			generator.build(rs);
			generator.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}
		System.out.println("end");
	}

	public static String fileToString(String file) {

		FileReader fileReader;
		StringBuffer sql = new StringBuffer();
		try {
			fileReader = new FileReader(file);
			BufferedReader reader = new BufferedReader(fileReader);

			String line = null;
			while ((line = reader.readLine()) != null) {
				sql.append(line);
				sql.append("\n");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return sql.toString();

	}

	public static void buildCVSByFile(String tableName, String fileName) {
		System.out.println("table name:" + tableName);
		PreparedStatement ps;
		ResultSet rs;

		DBConnectorGSBN connector = new DBConnectorGSBN();

		String sql = fileToString(fileName);

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			CSVGenerator generator = new CSVGenerator(new File("c:\\gsbndb\\"
					+ tableName + ".csv"));
			generator.build(rs);
			generator.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}
		System.out.println("end");
	}
}
